package jnpf.onlinedev.util.onlineDevUtil;

import cn.hutool.core.util.ObjectUtil;
import com.alibaba.fastjson.JSONArray;
import jnpf.base.model.template6.ColumnListField;
import jnpf.base.util.VisualUtils;
import jnpf.database.model.DbFieldMod;
import jnpf.database.util.DataSourceUtil;
import jnpf.database.util.DbTypeUtil;
import jnpf.exception.DataException;
import jnpf.database.entity.DbLinkEntity;
import jnpf.database.util.JdbcUtil;
import jnpf.model.visiual.JnpfKeyConsts;
import jnpf.model.visiual.TableModel;
import jnpf.onlinedev.model.OnlineDevListModel.*;
import jnpf.util.StringUtil;
import jnpf.util.context.SpringContext;
import lombok.Cleanup;
import lombok.SneakyThrows;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.*;
import java.util.stream.Collectors;

/**
 *
 *
 * @author JNPF开发平台组
 * @version V3.2.0
 * @copyright 引迈信息技术有限公司（https://www.jnpfsoft.com）
 * @date  2021/7/28
 */
public class OnlineDatabaseUtils {
	public static List<OnlineDevListDataVO> getTableDataList(Connection conn, String sql, String pKeyName,List<OnlineColumnFieldModel> childFieldList)  {
		List<OnlineDevListDataVO> list = new ArrayList<>();
		try {
			List<List<DbFieldMod>> fieldMods = JdbcUtil.queryIncludeFieldMods(conn, sql);
			List<Map<String,Object>> dataList = new ArrayList<>();

			for (List<DbFieldMod> dblist : fieldMods){
				Map<String,Object> dataMap =new HashMap<>();
				HashMap<String, String> collect = dblist.stream().collect(Collectors.toMap(s -> Optional.ofNullable(s.getColumnName()).orElse(""), s -> Optional.ofNullable(s.getColumnValue()).orElse(""), (a, b) -> b, HashMap::new));
				dataMap.putAll(collect);
				for (OnlineColumnFieldModel on : childFieldList){
					//需要替换的子表字段
					DbFieldMod fieldMod = dblist.stream()
							.filter(dbMod -> dbMod.getTableName().equalsIgnoreCase(on.getTableName())
									&& dbMod.getColumnName().equalsIgnoreCase(on.getField())).findFirst().orElse(null);
					//将数据转成map格式
					if (ObjectUtil.isNotEmpty(fieldMod)){
						dataMap.remove(fieldMod.getColumnName());
						dataMap.put(on.getOriginallyField(),fieldMod.getColumnValue());
					}
				}
				dataList.add(dataMap);
			}
			for (Map<String, Object> dataMap : dataList) {
				OnlineDevListDataVO dataVo = new OnlineDevListDataVO();
				dataMap = toLowerKey(dataMap);
				dataVo.setData(dataMap);
				if (dataMap.containsKey(pKeyName.toUpperCase())) {
					dataVo.setId(String.valueOf(dataMap.get(pKeyName.toUpperCase())));
				}
				list.add(dataVo);
			}
		} catch (DataException e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 生成列表sql语句
	 * @param classifyFieldList 子表字段集合
	 * @param listSqlModel 列表sql语句模型
	 * @param linkEntity 数据源
	 * @return
	 */
	public static String getListSql(List<OnlineColumnChildFieldModel> classifyFieldList, OnlineListSqlModel listSqlModel, DbLinkEntity linkEntity){
		StringBuilder sql = new StringBuilder();
		DataSourceUtil dataSourceUtil = SpringContext.getBean(DataSourceUtil.class);
		String mainTable = listSqlModel.getMainTable();

		//金仓 oracle 需关闭大小写敏感
		//数据源
		if (linkEntity!=null){
			if (DbTypeUtil.checkKingbase(linkEntity)){
				mainTable =  linkEntity.getUserName() + "." + listSqlModel.getMainTable() ;
			}
		}
		//默认数据源
		else {
			if (DbTypeUtil.checkKingbase(dataSourceUtil)){
				mainTable =  linkEntity.getUserName() + "." + listSqlModel.getMainTable() ;
			}
		}
		{
			if (classifyFieldList.size()>0){
				sql.append("select " + listSqlModel.getFields() + " from" + " " + mainTable);
				for (OnlineColumnChildFieldModel model : classifyFieldList){
					sql.append(" LEFT JOIN " + model.getTable() + " ON " + model.getTable()+"."+model.getTableField() + "=" + mainTable + "." + model.getRelationField());
				}
				sql.append(listSqlModel.getResultSql() + " ORDER BY ");
			}else {
				sql.append("select " + listSqlModel.getFields() + " from" + " " + mainTable + listSqlModel.getResultSql() + " ORDER BY ");
			}
			if (!StringUtil.isEmpty(listSqlModel.getDefaultSidx())) {
				sql.append(listSqlModel.getDefaultSidx() + " " + listSqlModel.getSort());
			} else {
				sql.append(listSqlModel.getPKeyName() + " " + listSqlModel.getSort());
			}
		}
		return sql.toString();
	}

	public static Map<String, Object> toLowerKey(Map<String, Object> map) {
		Map<String, Object> resultMap = new HashMap<>(16);
		Set<String> sets = map.keySet();
		for (String key : sets) {
			resultMap.put(key.toLowerCase(), map.get(key));
		}
		return resultMap;
	}

	public static Boolean existKey(List<String> feilds, String pKeyName) {
		if (feilds.size() > 0) {
			for (String feild : feilds) {
				if (feild.equals(pKeyName)) {
					return true;
				}
			}
		}
		return false;
	}

	public static List<OnlineDevListDataVO> setDataId(String keyName, List<OnlineDevListDataVO> DevList) {
		keyName = keyName.toLowerCase();
		for (OnlineDevListDataVO dataVo : DevList) {
			Map<String, Object> dataMap = dataVo.getData();
			if (dataMap.get(keyName) != null) {
				dataVo.setId(String.valueOf(dataMap.get(keyName)));
			}
		}
		return DevList;
	}

	@SneakyThrows
	public static List<OnlineDevListDataVO> getDataInTable(TableModel tableModel, List<OnlineDevListDataVO> dataVOList, DbLinkEntity linkEntity, List<ColumnListField> modelList, List<VisualColumnSearchVO> searchVOList)  {
			@Cleanup Connection connection = VisualUtils.getDataConn(linkEntity);

			String mainTable = tableModel.getTable();
			String primaryKey=VisualUtils.getpKey(connection,mainTable);
			//列表字段
			List<String> columnDataFields = modelList.stream().map(field-> field.getProp()).collect(Collectors.toList());
			String columnDataField= String.join(",",columnDataFields);
		String sql;
		if (searchVOList!=null){
			//条件字段
			StringBuilder searchField = new StringBuilder();
			for (VisualColumnSearchVO searchVO : searchVOList){
				String jnpfKey = searchVO.getConfig().getJnpfKey();
				Boolean isMultiple = searchVO.getMultiple();
				if (searchVO.getSearchType().equals("1")){
					searchField.append(searchVO.getVModel()+"=? and");
				}
				if (searchVO.getSearchType().equals("2")){
					if (isMultiple){

					}
					searchField.append(searchVO.getVModel()+"like '%?'% and");
				}
				if (searchVO.getSearchType().equals("3")){

				}
			}

			 sql = "Select "+columnDataField+" from "+mainTable+" where " + searchField;
		}else {
			sql = "Select "+columnDataField+" from "+mainTable+" where 1=1 ";
		}

		@Cleanup PreparedStatement preparedStatement =connection.prepareStatement(sql);
		int i =1;
		for (VisualColumnSearchVO searchVO : searchVOList){
			preparedStatement.setObject(i,searchVO.getValue());
		}
		return null;
	}


	public static List<Object> getValueList(List<VisualColumnSearchVO> searchVOList){
		List<Object> valueList = new LinkedList<>();
		for (VisualColumnSearchVO vo : searchVOList){
			String jnpfKey = vo.getConfig().getJnpfKey();
			String format;
			switch (jnpfKey){
				case JnpfKeyConsts.MODIFYTIME:
				case JnpfKeyConsts.CREATETIME:
				case JnpfKeyConsts.DATE:
					JSONArray timeStampArray = (JSONArray) vo.getValue();
					Long o1 =(Long) timeStampArray.get(0);
					Long o2 = (Long) timeStampArray.get(1);
					format = StringUtil.isEmpty(vo.getFormat()) ? "yyyy-MM-dd HH:mm:ss" : vo.getFormat();
					//时间戳转string格式
					String startTime = OnlinePublicUtils.getDateByFormat(o1,format);
					String endTime = OnlinePublicUtils.getDateByFormat(o2,format);
					//处理时间查询条件范围
					endTime=endTime.substring(0,10);
					String firstTimeDate = OnlineDatabaseUtils.getTimeFormat(startTime);
					String lastTimeDate =  OnlineDatabaseUtils.getLastTimeFormat(endTime);
					valueList.add(firstTimeDate);
					valueList.add(lastTimeDate);
					break;
				case JnpfKeyConsts.TIME:
					JSONArray timeArray =(JSONArray)vo.getValue();
					String start = String.valueOf(timeArray.get(0));
					String end =String.valueOf(timeArray.get(1));
					valueList.add(start);
					valueList.add(end);
					break;
				case JnpfKeyConsts.NUM_INPUT:
				case JnpfKeyConsts.CALCULATE:
					List<String> searchArray = (List<String>)vo.getValue();
					Integer firstValue = null;
					Integer secondValue = null;
					for(int i=0;i<searchArray.size();i++){
						String name = searchArray.get(i);
						if(StringUtil.isNotEmpty(name)) {
							if (i == 0) {
								firstValue =Integer.valueOf(name);
							} else {
								secondValue =Integer.valueOf(name);
							}
						}
					}
					if (ObjectUtil.isNotEmpty(firstValue)){
						valueList.add(firstValue);
					}
					if (ObjectUtil.isNotEmpty(secondValue)){
						valueList.add(secondValue);
					}
					break;
				default:
					valueList.add(vo.getValue());
					break;
			}
		}
		return valueList;
	}

	/**
	 * 转换时间格式
	 * @param time
	 * @return
	 */
	public static String getTimeFormat(String time){
		String result;
		switch (time.length()){
			case 16:
				result=time+":00";
				break;
			case 19:
				result=time;
				break;
			case 21:
				result=time.substring(0,time.length()-2);
				break;
			case 10:
				result=time+" 00:00:00";
				break;
			case 8:
				result="2000-01-01 "+time;
				break;
			default:
				result="";
				break;
		}
		return result;
	}

	public static String getLastTimeFormat(String time){
		String result;
		switch (time.length()){
			case 16:
				result=time+":00";
				break;
			case 19:
				result=time;
				break;
			case 10:
				result=time+" 23:59:59";
				break;
			case 8:
				result="2000-01-01 "+time;
				break;
			default:
				result="";
				break;
		}
		return result;
	}

}
